package models;

import play.*;
import play.data.validation.MaxSize;
import play.db.jpa.*;

import javax.persistence.*;
import javax.persistence.FetchType;
import java.util.*;

import flexjson.JSONSerializer;
import flexjson.transformer.DateTransformer;

/**
 * LZJD_CMS entity 
 * desc: 廉洁风险防控内网发布平台 
 */
@Entity 
@Table(name="LZJD_Document")
public class Document extends Model {

	public String title;

    @MaxSize(65535)
    @Basic(fetch=FetchType.LAZY)
    @Column(name="content", length=65535)
    public String content;

    @ManyToOne
    public SupervisionList supervisionList;
    
    @ManyToOne
    public User creator;

    public Date createTime;

    public Date updateTime;

    public String keywords;

    public String remark;

    public Boolean hide; 

    public String attachment;

    @Transient
    public String creatorName;
    
    public String getcreatorName() {
        if (creator !=null) return creator.realName;
        else return "";
    }
  
    public String toString() {
        return title;
    }

    public static String toJson(List<Document> documents) {
        return new JSONSerializer()
          .transform(new DateTransformer("yyyy-MM-dd HH:mm"), "updateTime")
          .include("id",  "title", "content", "hide", "keywords", "supervisionList.id",
            "creatorName", "creator.id", "creator.realName", "updateTime")
          .exclude("*")
          .serialize(documents);
    }
    
    public static String toSimpleJson(List<Document> documents) {
        return new JSONSerializer()
          .include("id",  "title", "creatorName", "creator.name", "updateTime")
          .exclude("*")
          .serialize(documents);
    }

    public static List<Document> getDocumentsBySupervision(long svid) {
        return getDocumentsBySupervisionList(svid);
    }

    public static List<Document> getDocumentsByType(String svtitle) {
        SupervisionList supervisionList = SupervisionList.findByTitle(svtitle);
        return getDocumentsBySupervisionList(supervisionList.id);
    }
    

    public static List<Document> getDocumentsBySupervisionList(long svid) {
        String sql ="with supervision_cte (id, title, parent_id) "
        + "as "
        + "( "
        + "select id, title, parent_id from LZJD_SupervisionList where id = #svid "
    
        + "union all "
    
        + "select l.id, l.title, l.parent_id from LZJD_SupervisionList l Inner Join supervision_cte cte "
        + "on l.parent_id = cte.id "
        + ") "

        + "select d.* " 
        + " from supervision_cte cte inner join LZJD_Document d "
        + "on cte.id = d.supervisionList_id "
        + "where d.hide <> 1 "
        + "order by d.updateTime desc " ;
        // "OPTION (MAXRECURSION 2)"
        
        sql = sql.replaceAll("#svid", Long.toString(svid));        
        Query query = JPA.em().createNativeQuery(sql, Document.class);

        return query.getResultList();
    }

}